package com.maxd.respository.userinfo;

import com.maxd.model.UserInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface UserRepository extends Repository<UserInfo, Long> {

    @Query(value = "from UserInfo s where s.phone=:phone order by id desc")
    Page<UserInfo> find(@Param("phone") String phone, Pageable pageable);

    @Query(value = "select s.createAt,s.image_url,s.nickName,s.relationId,s.balance from UserInfo s where s.invitation=:invitation order by id desc")
    Page<UserInfo> getUsersByInvitation(@Param("invitation") String invitation, Pageable pageable);

    @Query(value = "select * from user_info s where s.phone=:phone and if(:gradeIndex!='',grade_index=:gradeIndex,1=1) order by s.order_jifen desc",nativeQuery = true)
    Page<UserInfo> findOrderJifenByPhone(@Param("phone") String phone, Pageable pageable,String gradeIndex);

    @Query(value = "select * from user_info s where s.phone=:phone and if(:gradeIndex!='',grade_index=:gradeIndex,1=1) order by s.jifen desc",nativeQuery = true)
    Page<UserInfo> findJifenByPhone(@Param("phone") String phone, Pageable pageable,String gradeIndex);

    @Query(value = "select * from user_info s where if(:gradeIndex!='',grade_index=:gradeIndex,1=1) order by s.order_jifen desc",nativeQuery = true)
    Page<UserInfo> findOrderJifenList(Pageable pageable,String gradeIndex);

    @Query(value = "select * from user_info s where if(:gradeIndex!='',grade_index=:gradeIndex,1=1) order by s.jifen desc",nativeQuery = true)
    Page<UserInfo> findJifenByPhoneList(Pageable pageable,String gradeIndex);

    @Query(value = "select s.id from UserInfo s where s.invitation=:invitation order by id desc")
    List<Long> getUsersByInvitations(@Param("invitation") String invitation);

    /*@Query(value = "select id,jifen,if(nickName is not null ,nickName,concat('用户：',id,'')) as nickName from (SELECT id,if(u.nick_name is not null ,u.nick_name,u.phone) as nickName,ifnull((select sum(s.invitation_money) from publisher_order_dto_bean s where s.relation_id=u.relation_id and s.tk_status=3),0) as jifen  from user_info u where u.invitation=:invitation )  a order by jifen desc "
            ,countQuery = "select count(*) from user_info s where s.invitation=:invitation",
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitations1(Pageable pageable, @Param("invitation") String invitation);

    @Query(value = "select id,jifen,if(nickName is not null ,nickName,concat('用户：',id,'')) as nickName from (SELECT id,if(u.nick_name is not null ,u.nick_name,u.phone) as nickName,ifnull((select sum(s.invitation_money) from publisher_order_dto_bean s where s.relation_id=u.relation_id and s.tk_status= 3 ),0) as jifen FROM user_info u WHERE " +
            "u.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)) a order by jifen "
            ,countQuery = "SELECT count(*) FROM user_info s WHERE " +
            "s.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)",
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitationss(Pageable pageable,@Param("invitation") String invitation);*/

    @Query(value = "select id,is_tuan,jifen,if(nickName is not null ,nickName,concat('用户：',id,'')) as nickName from (SELECT id,is_tuan,if(u.nick_name is not null ,u.nick_name,u.phone) as nickName,ifnull((select sum(s.invitation_money) from publisher_order_dto_bean s where s.relation_id=u.relation_id and s.tk_status=3),0) as jifen  from user_info u where u.invitation=:invitation )  a order by jifen desc "
            ,countQuery = "select count(*) from user_info s where s.invitation=:invitation",
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitations1(Pageable pageable, @Param("invitation") String invitation);

    @Query(value = "select id,is_tuan,jifen,if(nickName is not null ,nickName,concat('用户：',id,'')) as nickName from (SELECT id,is_tuan,if(u.nick_name is not null ,u.nick_name,u.phone) as nickName,ifnull((select sum(s.invitation_money) from publisher_order_dto_bean s where s.relation_id=u.relation_id and s.tk_status= 3 ),0) as jifen FROM user_info u WHERE " +
            "u.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)) a order by jifen "
            ,countQuery = "SELECT count(*) FROM user_info s WHERE " +
            "s.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)",
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitationss(Pageable pageable,@Param("invitation") String invitation);


    /*@Query(value = "select * from (select u.id as id,IF( u.nick_name IS NOT NULL, u.nick_name, u.phone ) AS nickName,ifnull(sum( s.invitation_money ),0) as jifen from publisher_order_dto_bean s " +
            "left join user_info u on  s.relation_id=u.relation_id " +
            "where s.invitation=:invitation AND s.tk_status IN ( 12, 3, 14 ) " +
            "GROUP BY u.id,u.nick_name " +
            ") a order by jifen"
            ,countQuery = "select count(*) from (select u.id as id,IF( u.nick_name IS NOT NULL, u.nick_name, u.phone ) AS nickName,ifnull(sum( s.invitation_money ),0) as jifen from publisher_order_dto_bean s " +
            "left join user_info u on  s.relation_id=u.relation_id " +
            "where s.invitation=:invitation AND s.tk_status IN ( 12, 3, 14 ) " +
            "GROUP BY u.id,u.nick_name " +
            ") a order by jifen"
            ,
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitations1(Pageable pageable, @Param("invitation") String invitation);

    @Query(value = "select * from (select u.id as id,IF( u.nick_name IS NOT NULL, u.nick_name, u.phone ) AS nickName,ifnull(sum( s.invitation_moneys ),0) as jifen from publisher_order_dto_bean s " +
            "left join user_info u on  s.relation_id=u.relation_id " +
            "where s.invitations=:invitation AND s.tk_status IN ( 12, 3, 14 ) " +
            "GROUP BY u.id,u.nick_name " +
            ") a order by jifen"
            ,countQuery = "select count(*) from (select u.id as id,IF( u.nick_name IS NOT NULL, u.nick_name, u.phone ) AS nickName,ifnull(sum( s.invitation_moneys ),0) as jifen from publisher_order_dto_bean s " +
            "left join user_info u on  s.relation_id=u.relation_id " +
            "where s.invitations=:invitation AND s.tk_status IN ( 12, 3, 14 ) " +
            "GROUP BY u.id,u.nick_name " +
            ") a order by jifen"
            ,
            nativeQuery = true)
    Page<Map<String,Object>> getUsersByInvitationss(Pageable pageable,@Param("invitation") String invitation);
*/


    @Query(value = "from UserInfo s where s.invitation=:invitation and s.balance=0")
    List<UserInfo> getInvitation(@Param("invitation") String invitation);

    @Query(value = "from UserInfo s where s.invitation=:invitation")
    Page<UserInfo> getInvitations(@Param("invitation") String invitation,Pageable pageable);

    @Query(value = "select * from user_info where invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)",nativeQuery = true
    )
    Page<UserInfo> getInvitationss(@Param("invitation") String invitation,Pageable pageable);

    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation")
    String getCount(@Param("invitation") String invitation);


    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation and t.balance=0")
    String getCounts(@Param("invitation") String invitation);

    @Query(value = "select count(t.id) from UserInfo t")
    String getCountNumber();

    @Query(value = "select count(t.id) from UserInfo t where t.isInvitation=:isInvitation")
    String getCountNumberMember(@Param("isInvitation") int isInvitation);

    @Query(value = "select count(*) from user_info where relation_id is not null",nativeQuery = true)
    Integer getUserInfoByNotRelationId();

    @Query(value ="select count(*) from user_info where relation_id is not null",nativeQuery = true)
    Integer selectUserInfoByNotRelationId();

    @Query(value ="select count(*) from user_info where relation_id is null",nativeQuery = true)
    Integer selectUserInfoByRelationIdIsNull();

    @Query(value ="select count(*) from user_info where jifen is not null",nativeQuery = true)
    Integer selectUserInfoByNotJifen();

    @Query(value = "select count(s.id) from UserInfo s where s.isInvitation=:isInvitation and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTime(@Param("time") String time, @Param("isInvitation") int isInvitation);

    @Query(value = "select count(s.id) from UserInfo s where s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTime(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.relationId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByRelationId(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.relationId is null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeRAndRelationIdIsNull(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.phone is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByPhone(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.openId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByWx(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.mpId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByMp(@Param("time") String time);


    @Query(value = "select count(s.id) from UserInfo s where s.platform=:platform and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeAndPlatform(@Param("time") String time, @Param("platform") String platform);

    @Query(value = "select count(s.id) from UserInfo s where s.platform=:platform and s.createAt like concat('%',:createAt,'%')")
    String getCountUser(@Param("platform") String platform, @Param("createAt") String createAt);

    @Query(value = "from UserInfo s where s.phone=:userName")
    UserInfo login(@Param("userName") String userName);

    @Query(value = "from UserInfo s where s.id=:id")
    UserInfo getUserById(@Param("id") Long id);

    @Query(value = "from UserInfo s where s.clientid=:clientid")
    UserInfo getUserByClientid(@Param("clientid") String clientid);

    @Query(value = "from UserInfo s where s.phone=:userName")
    UserInfo getUserByPhone(@Param("userName") String userName);

    @Query(value = "from UserInfo s where s.appleUserId=:appleUserId")
    UserInfo getUserInfoByAppleUserId(@Param("appleUserId") String appleUserId);

    @Query(value = "from UserInfo s where s.pdd=:pdd")
    UserInfo getUserByPdd(@Param("pdd") String pdd);

    @Query(value = "from UserInfo s where s.jd=:jd")
    UserInfo getUserByJd(@Param("jd") String jd);

    @Query(value = "from UserInfo s where s.openId=:openId")
    UserInfo getUserByWxId(@Param("openId") String openId);

    @Query(value = "from UserInfo s where s.unionid=:unionid")
    UserInfo getUserByUnionid(@Param("unionid") String unionid);

    @Query(value = "from UserInfo s where s.open_uid=:open_uid")
    UserInfo getUserByOpen_uid(@Param("open_uid") String open_uid);

    @Query(value = "from UserInfo s where s.tb_openid=:tb_openid")
    UserInfo getUserByTbOpenid(@Param("tb_openid") String tb_openid);

    @Query(value = "from UserInfo s where s.mpId=:mpId")
    UserInfo getUserByMpId(@Param("mpId") String mpId);


    @Query(value = "from UserInfo s where s.relationId=:relationId")
    UserInfo getUserByRelationId(@Param("relationId") String relationId);

    @Query(value = "from UserInfo s where s.invitationCode=:invitationCode")
    UserInfo getUserByInvitationCode(@Param("invitationCode") String invitationCode);

    @Query(value = "from UserInfo s where s.relationId<>null")
    List<UserInfo> findByRelationIdNotNull();

    @Query(value = "select s.invitation from UserInfo s group by s.invitation")
    List<String> findRelationList();

    @Modifying
    @Query(value = "update UserInfo s set s.state=:state where s.id=:id")
    Integer updateState(@Param("state") String state, @Param("id") long id);

    @Modifying
    @Query(value = "update UserInfo s set s.pwd=:pwd where s.id=:id")
    Integer updatePwd(@Param("pwd") String pwd, @Param("id") String id);

    @Query(value = "select s.invitation from UserInfo s where s.createAt like concat('%',:time,'%') group by s.invitation")
    List<Long> findInvitationList(@Param("time") String time);

    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation and t.createAt like concat('%',:time,'%')")
    Long getCountByTime(@Param("invitation") String invitation,@Param("time") String time);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.jifen=s.jifen+:jifen where s.id=:id")
    Integer updateJiFenAdd(@Param("jifen") String jifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.jifen=s.jifen-:jifen where s.id=:id")
    Integer updateJiFenDelete(@Param("jifen") String jifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=:orderJifen where s.id=:id")
    Integer updateOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);

    @Query(value = "from UserInfo")
    List<UserInfo> selectUserList();

    //积分抽奖：加分
    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=s.orderJifen+:orderJifen where s.id=:id")
    Integer addOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);
    //积分抽奖：减分
    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=s.orderJifen-:orderJifen where s.id=:id")
    Integer lessOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.nickName=:nickName where s.id=:userId")
    Integer updateNickName(@Param("nickName") String nickName,@Param("userId") Long userId);


    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.image_url=:imageUrl where s.id=:userId")
    Integer updateImgUrl(@Param("imageUrl") String imageUrl,@Param("userId") Long userId);

    //直属用户id列表
    @Query(value = "select s.id from UserInfo s where s.invitation=:relationId order by id desc")
    List<Long> getUsersByRelationId(@Param("relationId") String relationId);

    //非直属用户id列表
    @Query(value = "select s.id from user_info s where invitation in (select relation_id from user_info u where u.invitation=:relationId)",nativeQuery = true)
    List<Long> getUsersByRelationIds(@Param("relationId") String relationId);

    /**
     * laban 新增 根据userid修改
     */
    @Modifying
    @Transactional
    @Query(nativeQuery = true,value = "update user_info set nick_name=:#{#userInfo.nickName}," +
            "phone=:#{#userInfo.phone},zhifubao=:#{#userInfo.zhifubao},pwd=:#{#userInfo.pwd}," +
            "zhifubao_name=:#{#userInfo.zhifubaoName},image_url=:#{#userInfo.image_url} where id=:#{#userInfo.id}")
    void updateHelpTaskUserById(@Param("userInfo") UserInfo userInfo);

    /**@Param("issysuser") String issysuser,
     * laban 新增 phoneand s.is_sys_user=:issysuser
     */
    @Query(value = "select * from user_info where is_sys_user='2' and phone=:phone  order by id desc",nativeQuery = true)
    Page<UserInfo> findByPhoneAndSysUser(@Param("phone") String phone, Pageable pageable);

    @Query(value = "select * from user_info where is_sys_user='2' order by id desc",nativeQuery = true)
    Page<UserInfo> findListByTwo(Pageable pageable);

    @Modifying
    @Transactional
    @Query(value = "update user_info set clientid='' where clientid=:clientid",nativeQuery = true)
    int updateUserClientIdIsNull(String clientid);

    @Query(value = "select count(*) from user_info where invitation=:relationId and create_at like concat('%',:time,'%')",nativeQuery = true)
    int selectUserByZhiCountByTime(@Param("relationId") String relationId,@Param("time") String time);

    @Query(value = "select count(*) from user_info where invitation in (select relation_id from user_info where invitation=:relationId) and create_at like concat('%',:time,'%')",nativeQuery = true)
    int selectUserByFeiCountByTime(@Param("relationId") String relationId,@Param("time") String time);

    @Query(value = "select count(*) from user_info where invitation=:relationId",nativeQuery = true)
    int selectUserByZhiCount(@Param("relationId") String relationId);

    @Query(value = "select * from user_info order by id desc",nativeQuery = true)
    Page<UserInfo> findPage(Pageable pageable);

}
